package com.configtools.generated.container;

import java.io.File;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.nio.file.Files;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.CopyOnWriteArrayList;
import java.util.function.Supplier;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

import org.apache.commons.codec.digest.DigestUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.configtools.generated.bean.BaseCfgBean;

/**
 * 配置表容器
 *
 * @author Cursor
 */
public abstract class BaseCfgContainer<T extends BaseCfgBean> {

  protected Logger logger = LoggerFactory.getLogger(this.getClass());

  // region============================== 模板 =============================
  /** 字段描述列 */
  protected int fieldDescRow = 0;
  /** 字段类型列 */
  protected int fieldTypeRow = 1;
  /** 字段名列 */
  protected int fieldNameRow = 2;
  /** 字段数值范围列 */
  protected int fieldDataRangeRow = 3;
  /** 数据读取开始行数 */
  protected int dataStartRow = 4;
  // endregion============================== 模板 ==============================

  /** cfgBeanMap key: 配置的ID, 配置的数据 */
  protected Map<Integer, T> cfgBeanMap = Collections.emptyMap();

  /** excel解析异常收集器 */
  protected List<Exception> exceptionCollectors = new CopyOnWriteArrayList<>();

  /** excel文件的md5码 */
  protected Map<String, String> md5CacheMap = Collections.emptyMap();

  

  public BaseCfgContainer() {}

  /**
   * 获取excel文件名 auto gen
   *
   * @return excel文件名
   */
  public abstract String getExcelName();


  /**
   * 获取新的配置表bean
   *
   * @return cfgBean
   */
  protected abstract T createNewBean();

  /**
   * 创建新的容器
   *
   * @return 新容器
   */
  public abstract BaseCfgContainer<T> getNewContainer();

  /**
   * 加载数据
   *
   * @param resourceRootPath excel资源根路径
   * @throws Exception e
   */
  public synchronized void loadData(String resourceRootPath) throws Exception {
    if (resourceRootPath == null || resourceRootPath.isEmpty()) {
      throw new RuntimeException("Excel资源根路径不能为空");
    }
    
    // 检查和获取绑定的excel文件
    File file = checkAndGetExcelFile(resourceRootPath);
    Map<Integer, T> tempCfgMap = new ConcurrentHashMap<>(8);
    Map<String, String> md5HexMap = new ConcurrentHashMap<>(1);
    
    try {
      // 计算文件的md5值
      String md5Hex = DigestUtils.md5Hex(Files.newInputStream(file.toPath()));
      md5HexMap.put(file.getName(), md5Hex);
      
      // 打开Excel工作簿
      Workbook wb = WorkbookFactory.create(file, null, true);
      Sheet sheet = wb.getSheetAt(0);
      
      logger.info("开始解析Excel文件: {}, Sheet: {}", file.getName(), sheet.getSheetName());
      
      // 加载字段信息
      Map<Integer, ExcelFieldInfo> excelFieldInfoMap = loadFieldInfo(sheet, sheet.getRow(0));
      // 获取Bean字段信息
      Map<String, Field> cfgBeanFieldMap = getAllFieldsMap();
      // 获取需要跳过的列
      Set<Integer> skipCellList = getSkipCellList(sheet);
      
      // 检查字段匹配
      checkExcelFieldMatchBeanField(excelFieldInfoMap, cfgBeanFieldMap, sheet, file);
      
      // 解析数据行
      int successCount = 0;
      int errorCount = 0;
      for (int i = getDataStartRow(); i <= sheet.getLastRowNum(); i++) {
        try {
          T cfgBean = createNewBean();
          Row row = sheet.getRow(i);
          
          // 检查是否是空行
          if (isBlankRow(row)) {
            continue;
          }
          
          // 处理单行数据
          handleRowData(row, cfgBean, cfgBeanFieldMap, excelFieldInfoMap, skipCellList);
          
          // 检查ID是否重复
          if (!tempCfgMap.containsKey(cfgBean.getSid())) {
            tempCfgMap.put(cfgBean.getSid(), cfgBean);
            successCount++;
          } else {
            String errorMsg = String.format("第%d行ID重复: %d", i + 1, cfgBean.getSid());
            logger.error(errorMsg);
            exceptionCollectors.add(new ExcelDataParseException(file.getName(), errorMsg));
            errorCount++;
          }
        } catch (Exception e) {
          String errorMsg = String.format("第%d行解析失败: %s", i + 1, e.getMessage());
          logger.error(errorMsg, e);
          exceptionCollectors.add(new ExcelDataParseException(file.getName(), errorMsg));
          errorCount++;
        }
      }
      
      // 设置结果
      if (tempCfgMap.isEmpty()) {
        logger.warn("容器{} 加载数据为空", getClass().getSimpleName());
      } else {
        logger.info("解析完成 - 成功: {}行, 失败: {}行", successCount, errorCount);
      }
      
      cfgBeanMap = Collections.unmodifiableMap(tempCfgMap);
      
      // 关闭工作簿
      wb.close();
      
    } catch (Exception e) {
      logger.error("解析Excel文件失败: {}", file.getName(), e);
      throw e;
    }
    
    // 如果有解析异常，抛出异常
    if (!exceptionCollectors.isEmpty()) {
      logger.error("Excel解析过程中发现{}个错误", exceptionCollectors.size());
      throw new ContainerExceptionBlocker(exceptionCollectors);
    }
    
    // 保存md5值
    md5CacheMap = Collections.unmodifiableMap(md5HexMap);
  }

  /**
   * 检查excel字段是否和配置bean中的字段一致
   *
   * @param excelFieldInfoMap excel字段信息
   * @param cfgBeanFieldMap 配置表bean字段
   * @param sheet 工作薄
   * @param file excel文件
   */
  private void checkExcelFieldMatchBeanField(
      Map<Integer, ExcelFieldInfo> excelFieldInfoMap,
      Map<String, Field> cfgBeanFieldMap,
      Sheet sheet,
      File file) {
    // 获取Excel中的字段列表
    List<String> excelFieldList =
        excelFieldInfoMap.values().stream()
            .map(ExcelFieldInfo::getFieldName)
            .collect(Collectors.toList());
    
    // 获取Bean中的字段列表
    List<String> cfgBeanFieldList = new ArrayList<>(cfgBeanFieldMap.keySet());
    
    // 找出Bean中有但Excel中没有的字段
    List<String> missingFields = new ArrayList<>(cfgBeanFieldList);
    missingFields.removeAll(excelFieldList);
    
         // 如果配置表中的字段excel中不存在
     if (!missingFields.isEmpty()) {
       String errorMsg = String.format("Excel文件缺少字段: %s", String.join(", ", missingFields));
       logger.error(errorMsg);
       throw new ExcelDataParseException(
           sheet.getSheetName(), errorMsg);
     }
    
    logger.debug("字段检查通过，Excel字段数: {}, Bean字段数: {}", excelFieldList.size(), cfgBeanFieldList.size());
  }

  /**
   * 获取所有字段
   *
   * @return 字段列表
   */
  protected Map<String, Field> getAllFieldsMap() throws ClassNotFoundException {
    ParameterizedType genericInterfaces =
        (ParameterizedType) this.getClass().getGenericSuperclass();
    Type actualTypeArguments = genericInterfaces.getActualTypeArguments()[0];
    final Class<?> baseCfgBean = Class.forName(actualTypeArguments.getTypeName());
    final Map<String, Field> allFields = new HashMap<>(8);
    Class<?> currentClass = baseCfgBean;
    while (currentClass != null) {
      final Field[] declaredFields = currentClass.getDeclaredFields();
      for (Field declaredField : declaredFields) {
        allFields.putIfAbsent(declaredField.getName(), declaredField);
      }
      currentClass = currentClass.getSuperclass();
    }
    return allFields;
  }

  /** 处理单行数据 */
  private void handleRowData(
      Row row,
      T cfgBean,
      Map<String, Field> cfgBeanFieldMap,
      Map<Integer, ExcelFieldInfo> excelFieldInfoMap,
      Set<Integer> skipCellList) {
    List<Integer> colNumList = new ArrayList<>(excelFieldInfoMap.keySet());
    
    // 遍历单行中每列的数据
    for (int colNum : colNumList) {
      // 需要跳过的列
      if (skipCellList.contains(colNum)) {
        continue;
      }
      
      // 获取字段信息
      ExcelFieldInfo excelFieldInfo = excelFieldInfoMap.get(colNum);
      Cell cell = row.getCell(colNum);
      String fieldType = excelFieldInfo.getOriginFieldType();
      String fieldName = excelFieldInfo.getFieldName();
      
      Object cellData;
      if (cell == null) {
        if (colNum == 0) {
          // ID列不能为空
          String errorMsg = String.format("第%d行第%d列(ID列)不能为空", row.getRowNum() + 1, colNum + 1);
          logger.error(errorMsg);
          this.exceptionCollectors.add(
              new ExcelDataParseException(row.getSheet().getSheetName(), errorMsg));
          return;
        }
        // 为空时赋予默认值
        FieldDataAdapter fieldDataAdapter = FieldDataAdapter.getFieldAdapterByTypeStr(fieldType);
        cellData = fieldDataAdapter.getFieldAdapter().getDefaultVal();
      } else {
        try {
          // 解析excel中的数据
          cellData = parseCellData(fieldType, cell, cfgBean);
        } catch (Exception e) {
          // 数据解析异常
          String errorMsg = String.format("第%d行第%d列(%s)数据格式错误: %s, 值: %s", 
              row.getRowNum() + 1, colNum + 1, fieldName, e.getMessage(), getCellValue(cell));
          logger.error(errorMsg);
          this.exceptionCollectors.add(
              new ExcelDataParseException(excelFieldInfo, row, cell, errorMsg));
          return;
        }
      }
      
      // ID列不能为空
      if (colNum == 0 && cellData == null) {
        String errorMsg = String.format("第%d行第%d列(ID列)不能为空", row.getRowNum() + 1, colNum + 1);
        logger.error(errorMsg);
        this.exceptionCollectors.add(
            new ExcelDataParseException(excelFieldInfo, row, cell, errorMsg));
        return;
      }
      
      // 如果配置表中的字段未在bean中找到则跳过
      if (!cfgBeanFieldMap.containsKey(fieldName)) {
        logger.debug("跳过未定义的字段: {}", fieldName);
        continue;
      }
      
      // 设置字段值
      Field field = cfgBeanFieldMap.get(fieldName);
      field.setAccessible(true);
      try {
        field.set(cfgBean, cellData);
      } catch (Exception e) {
        String errorMsg = String.format("第%d行第%d列(%s)字段类型不匹配: %s", 
            row.getRowNum() + 1, colNum + 1, fieldName, e.getMessage());
        logger.error(errorMsg);
        this.exceptionCollectors.add(
            new ExcelDataParseException(excelFieldInfo, row, cell, errorMsg));
        return;
      }
    }
  }

  /**
   * 是否跳过当前列
   *
   * @return 是否跳过
   */
  private Set<Integer> getSkipCellList(Sheet sheet) {
    Set<Integer> skipCellList = new HashSet<>();
    Row dataFieldRangeRow = sheet.getRow(getDataStartRow());
    if (dataFieldRangeRow == null || dataFieldRangeRow.getLastCellNum() <= 0) {
      return skipCellList;
    }
    for (Cell cell : dataFieldRangeRow) {
      String cellValue = getCellValue(cell);
      if (!isEmptyString(cellValue) && "client".equalsIgnoreCase(cellValue)) {
        skipCellList.add(cell.getColumnIndex());
      }
    }
    return skipCellList;
  }

  /** 解析列数据 */
  protected Object parseCellData(String fieldType, Cell cell, T cfgBean) {
    FieldDataAdapter fieldDataAdapter = FieldDataAdapter.getFieldAdapterByTypeStr(fieldType);
    String cellString = getCellValue(cell);
    return fieldDataAdapter
        .getFieldAdapter()
        .parseFieldStrToClassType(cellString, fieldType, cfgBean);
  }

  public enum FieldDataAdapter {
    // byte
    BYTE(ByteFieldAdapter::new),
    // short
    SHORT(ShortFieldAdapter::new),
    // int
    INT(IntFieldAdapter::new),
    // Long
    LONG(LongFieldAdapter::new),
    // 字符串适配器
    STRING(StringFieldAdapter::new),
    // 浮点数适配器
    FLOAT(FloatFieldAdapter::new),
    // 双精度浮点数适配器
    DOUBLE(DoubleFieldAdapter::new),
         // bool适配器
     BOOLEAN(BooleanFieldAdapter::new),
    ;

    /** 字段适配器 */
    private final FieldAdapter<?> fieldAdapter;

    FieldDataAdapter(Supplier<FieldAdapter<?>> fieldAdapterSupplier) {
      this.fieldAdapter = fieldAdapterSupplier.get();
    }

    public FieldAdapter<?> getFieldAdapter() {
      return fieldAdapter;
    }

    public interface FieldAdapter<K> {

      /**
       * 解析字符串字段为目标数据
       *
       * @param fieldType 字段字符串
       * @param fieldStr 待转换的字符数据
       * @return classType
       */
      K parseFieldStrToClassType(String fieldStr, String fieldType, BaseCfgBean baseCfgBean);

      /**
       * 获取默认值
       *
       * @return 获取默认值
       */
      default K getDefaultVal() {
        return null;
      }

      /**
       * 获取可接受的类型字符串
       *
       * @return 类型字符串数组
       */
      Set<String> getAcceptTypeStr();

      /**
       * 获取转后的目标类型字符串
       *
       * @return 类型字符串
       */
      String getTargetFieldTypeStr(String fieldType);

      /**
       * 是否是基础类型
       *
       * @return 是否是基础类型
       */
      boolean isBaseType();
    }

    private static class ByteFieldAdapter implements FieldAdapter<Byte> {
      @Override
      public Byte parseFieldStrToClassType(String fieldStr, String fieldType, BaseCfgBean cfgBean) {
        return isEmptyString(fieldStr)
            ? getDefaultVal()
            : Byte.parseByte(filterFloatStrVal(fieldStr));
      }

      @Override
      public Byte getDefaultVal() {
        return 0;
      }

      @Override
      public Set<String> getAcceptTypeStr() {
        return new HashSet<>(Arrays.asList("[Bb]yte", "java.lang.Byte"));
      }

      @Override
      public String getTargetFieldTypeStr(String fieldType) {
        return "byte";
      }

      @Override
      public boolean isBaseType() {
        return true;
      }
    }

    private static class ShortFieldAdapter implements FieldAdapter<Short> {
      @Override
      public Short parseFieldStrToClassType(
          String fieldStr, String fieldType, BaseCfgBean cfgBean) {
        return isEmptyString(fieldStr)
            ? getDefaultVal()
            : Short.parseShort(filterFloatStrVal(fieldStr));
      }

      @Override
      public Short getDefaultVal() {
        return 0;
      }

      @Override
      public Set<String> getAcceptTypeStr() {
        return new HashSet<>(Arrays.asList("[Ss]hort", "java.lang.Short"));
      }

      @Override
      public String getTargetFieldTypeStr(String fieldType) {
        return "short";
      }

      @Override
      public boolean isBaseType() {
        return true;
      }
    }

    private static class IntFieldAdapter implements FieldAdapter<Integer> {
      @Override
      public Integer parseFieldStrToClassType(
          String fieldStr, String fieldType, BaseCfgBean cfgBean) {
        return isEmptyString(fieldStr)
            ? getDefaultVal()
            : Integer.parseInt(filterFloatStrVal(fieldStr));
      }

      @Override
      public Integer getDefaultVal() {
        return 0;
      }

      @Override
      public Set<String> getAcceptTypeStr() {
        return new HashSet<>(Arrays.asList("[Ii]nt", "[Ii]nteger", "java.lang.Integer"));
      }

      @Override
      public String getTargetFieldTypeStr(String fieldType) {
        return "Integer";
      }

      @Override
      public boolean isBaseType() {
        return true;
      }
    }

    private static class LongFieldAdapter implements FieldAdapter<Long> {
      @Override
      public Long parseFieldStrToClassType(String fieldStr, String fieldType, BaseCfgBean cfgBean) {
        return isEmptyString(fieldStr)
            ? getDefaultVal()
            : Long.parseLong(filterFloatStrVal(fieldStr));
      }

      @Override
      public Long getDefaultVal() {
        return 0L;
      }

      @Override
      public Set<String> getAcceptTypeStr() {
        return new HashSet<>(Arrays.asList("[Ll]ong", "java.lang.Long"));
      }

      @Override
      public String getTargetFieldTypeStr(String fieldType) {
        return "Long";
      }

      @Override
      public boolean isBaseType() {
        return true;
      }
    }

         private static class StringFieldAdapter implements FieldAdapter<String> {
       @Override
       public String parseFieldStrToClassType(
           String fieldStr, String fieldType, BaseCfgBean cfgBean) {
         return isEmptyString(fieldStr.trim()) ? getDefaultVal() : fieldStr;
       }

      @Override
      public Set<String> getAcceptTypeStr() {
        return new HashSet<>(Arrays.asList("[Ss]tring", "java.lang.String"));
      }

       @Override
       public String getTargetFieldTypeStr(String fieldType) {
         return "String";
       }

       @Override
       public boolean isBaseType() {
         return true;
       }
     }

    private static class FloatFieldAdapter implements FieldAdapter<Float> {

      @Override
      public Float parseFieldStrToClassType(
          String fieldStr, String fieldType, BaseCfgBean cfgBean) {
        return isEmptyString(fieldStr) ? getDefaultVal() : Float.parseFloat(fieldStr);
      }

      @Override
      public Float getDefaultVal() {
        return 0F;
      }

      @Override
      public Set<String> getAcceptTypeStr() {
        return new HashSet<>(Arrays.asList("[Ff]loat", "java.lang.Float"));
      }

      @Override
      public String getTargetFieldTypeStr(String fieldType) {
        return "Float";
      }

      @Override
      public boolean isBaseType() {
        return true;
      }
    }

    private static class DoubleFieldAdapter implements FieldAdapter<Double> {
      @Override
      public Double parseFieldStrToClassType(
          String fieldStr, String fieldType, BaseCfgBean cfgBean) {
        return isEmptyString(fieldStr) ? getDefaultVal() : Double.parseDouble(fieldStr);
      }

      @Override
      public Double getDefaultVal() {
        return 0D;
      }

      @Override
      public Set<String> getAcceptTypeStr() {
        return new HashSet<>(Arrays.asList("[Dd]ouble", "java.lang.Double"));
      }

      @Override
      public String getTargetFieldTypeStr(String fieldType) {
        return "Double";
      }

      @Override
      public boolean isBaseType() {
        return true;
      }
    }

    private static class BooleanFieldAdapter implements FieldAdapter<Boolean> {
      @Override
      public Boolean parseFieldStrToClassType(
          String fieldStr, String fieldType, BaseCfgBean cfgBean) {
        return isEmptyString(fieldStr) ? getDefaultVal() : Boolean.parseBoolean(fieldStr);
      }

      @Override
      public Boolean getDefaultVal() {
        return false;
      }

      @Override
      public Set<String> getAcceptTypeStr() {
        return new HashSet<>(Arrays.asList("[Bb]oolean", "[Bb]ool", "java.lang.Boolean"));
      }

      @Override
      public String getTargetFieldTypeStr(String fieldType) {
        return "Boolean";
      }

      @Override
      public boolean isBaseType() {
        return true;
      }
    }

    private static final Map<FieldDataAdapter, List<Pattern>> PATTERN_CACHE_MAP =
        new ConcurrentHashMap<>();

    /**
     * 查找类型字段
     *
     * @param typeStr 类型字符串
     * @return 目标类型字段
     */
    public static FieldDataAdapter getFieldAdapterByTypeStr(String typeStr) {
      // 将检查字符串左右进行除空字符串处理
      final String finalTypeStr = typeStr.trim();
      List<FieldDataAdapter> adapters = getFieldDataAdapterList();
      for (FieldDataAdapter value : adapters) {
        if (!PATTERN_CACHE_MAP.containsKey(value)) {
          Set<String> matchStr = value.getFieldAdapter().getAcceptTypeStr();
          PATTERN_CACHE_MAP.computeIfAbsent(
              value, k -> matchStr.stream().map(Pattern::compile).collect(Collectors.toList()));
        }
        if (PATTERN_CACHE_MAP.get(value).stream()
            .anyMatch(pattern -> pattern.matcher(finalTypeStr).matches())) {
          return value;
        }
      }
      throw new ExcelFormatException("typeStr: " + typeStr + " cannot found any field type match");
    }

         private static List<FieldDataAdapter> getFieldDataAdapterList() {
       return Arrays.stream(values())
           .sorted(
               (o1, o2) -> o1.getFieldAdapter()
                   .getClass()
                   .getSimpleName()
                   .compareTo(o2.getFieldAdapter().getClass().getSimpleName()))
           .collect(Collectors.toList());
     }

    

    private static String filterFloatStrVal(String floatValStr) {
      String trimStr = floatValStr.trim();
      return isEmptyString(trimStr)
          ? "0"
          : (floatValStr.indexOf(".") > 0
              ? trimStr.substring(0, floatValStr.indexOf("."))
              : trimStr);
    }
  }

  /** 检测空行 */
  private boolean isBlankRow(Row row) {
    if (row == null) {
      return true;
    }
    for (Cell cell : row) {
      if (cell != null && cell.getCellType() != CellType.BLANK) {
        return false;
      }
    }
    return true;
  }

  /**
   * 加载字段信息
   *
   * @param sheet 表信息
   * @param row 当前行信息
   * @return 字段信息
   */
  private Map<Integer, ExcelFieldInfo> loadFieldInfo(Sheet sheet, Row row) {
    Map<Integer, ExcelFieldInfo> excelFieldInfoMap = new HashMap<>(8);
    // 装载头部数据
    for (int colNum = 0; colNum <= row.getLastCellNum(); colNum++) {
      ExcelFieldInfo excelFieldInfo = new ExcelFieldInfo();
      // 加载字段类型
      excelFieldInfo.setOriginFieldType(loadFieldInfoData(sheet, colNum, getFieldTypeRow()));
      if (isEmptyString(excelFieldInfo.getOriginFieldType())) {
        continue;
      }
      // 加载字段名
      excelFieldInfo.setFieldName(loadFieldInfoData(sheet, colNum, getFieldNameRow()));
      if (isEmptyString(excelFieldInfo.getFieldName())) {
        continue;
      }
      excelFieldInfoMap.put(colNum, excelFieldInfo);
    }
    return excelFieldInfoMap;
  }

  /** 字符是否为空 */
  private static Boolean isEmptyString(String checkEmptyStr) {
    return checkEmptyStr == null || checkEmptyStr.trim().isEmpty();
  }

  /**
   * 加载单列字段信息
   *
   * @param sheet 工作簿
   * @param colNum 行号
   */
  private String loadFieldInfoData(Sheet sheet, int colNum, int rowNum) {
    if (rowNum < 0) {
      throw new ExcelDataParseException(
          sheet.getSheetName(),
          "加载字段信息错误 列数: " + (colNum + 1) + " 配置表名: " + sheet.getSheetName() + " 配置的行数小于0");
    }
    if (sheet.getRow(rowNum) != null) {
      Cell cell = sheet.getRow(rowNum).getCell(colNum);
      String cellVal = null;
      if (cell != null) {
        String cellValue = getCellValue(cell);
        cellVal = cellValue.trim();
      }
      if (colNum == 0 && isEmptyString(cellVal)) {
        // 兼容第一行不填的情况默认为int
        cellVal = "int";
      }
      return cellVal;
    }
    return null;
  }

  /**
   * 将工作单元中都转为字符串进行处理
   *
   * @param cell 工作单元
   * @return 字符串
   */
  public static String getCellValue(Cell cell) {
    String value;
    switch (cell.getCellType()) {
        // 字符串
      case STRING:
        value = cell.getStringCellValue();
        break;
        // Boolean
      case BOOLEAN:
        value = cell.getBooleanCellValue() + "";
        break;
        // 公式
      case NUMERIC:
        if (String.valueOf(cell.getNumericCellValue()).contains("E")) {
          DataFormatter dataFormatter = new DataFormatter();
          return dataFormatter.formatCellValue(cell);
        }
        value = cell.getNumericCellValue() + "";
        break;
        // 空值
      case BLANK:
      default:
        value = "";
        break;
    }
    return value;
  }

  /**
   * 检查文件是否存在或者文件名是否正确
   *
   * @return 检查完后的excel
   */
  private File checkAndGetExcelFile(String resourceRootPath) {
    String filePathName = getBindExcelPath(resourceRootPath);
    File file = new File(filePathName);
    
    if (!file.exists()) {
      String errorMsg = String.format("Excel文件不存在: %s", filePathName);
      logger.error(errorMsg);
      throw new ExcelDataParseException(getExcelName(), errorMsg);
    }
    
    if (file.isDirectory()) {
      String errorMsg = String.format("路径是目录而不是文件: %s", filePathName);
      logger.error(errorMsg);
      throw new ExcelDataParseException(getExcelName(), errorMsg);
    }
    
    if (!file.getName().toLowerCase().endsWith(".xlsx") && !file.getName().toLowerCase().endsWith(".xls")) {
      String errorMsg = String.format("文件不是Excel格式: %s", file.getName());
      logger.error(errorMsg);
      throw new ExcelDataParseException(getExcelName(), errorMsg);
    }
    
    logger.debug("Excel文件检查通过: {}", filePathName);
    return file;
  }

  /**
   * 获取绑定的excel
   *
   * @return excel名列表
   */
  protected String getBindExcelPath(String resourceRootPath) {
    String excelNameReplaceSeparator = getExcelName().replace("\\", File.separator);
          return resourceRootPath + File.separator + excelNameReplaceSeparator;
  }

  public static class ExcelFormatException extends RuntimeException {
    public ExcelFormatException(String message) {
      super(message);
    }
  }

  public static class ExcelDataParseException extends RuntimeException {
    final String excelFileName;
    ExcelFieldInfo excelFieldInfo;
    Row row;
    Cell cell;

    public ExcelDataParseException(String file, String message) {
      super(message);
      this.excelFileName = file;
    }

    public ExcelDataParseException(String file, Throwable e) {
      super(e);
      this.excelFileName = file;
    }

    public ExcelDataParseException(ExcelFieldInfo excelFieldInfo, Row row, Cell cell, String msg) {
      super(msg);
      this.excelFileName = row.getSheet().getSheetName();
      this.excelFieldInfo = excelFieldInfo;
      this.row = row;
      this.cell = cell;
    }

    public ExcelDataParseException(ExcelFieldInfo excelFieldInfo, Row row, Cell cell, Throwable e) {
      super(e);
      this.excelFileName = row.getSheet().getSheetName();
      this.excelFieldInfo = excelFieldInfo;
      this.row = row;
      this.cell = cell;
    }

    @Override
    public String getMessage() {
      if (row != null && cell != null && excelFieldInfo != null) {
        return String.format("文件: %s, 行: %d, 列: %d, 字段: %s, 类型: %s, 值: %s, 错误: %s",
            row.getSheet().getSheetName(),
            row.getRowNum() + 1,
            cell.getColumnIndex() + 1,
            excelFieldInfo.getFieldName(),
            excelFieldInfo.getOriginFieldType(),
            getCellValue(cell),
            super.getMessage());
      } else {
        return String.format("文件: %s, 错误: %s", excelFileName, super.getMessage());
      }
    }
  }

  /** 容器异常传递 */
  public static class ContainerExceptionBlocker extends Exception {
    private final List<Exception> exceptions;

    public ContainerExceptionBlocker(List<Exception> exceptions) {
      this.exceptions = exceptions;
    }

    public List<Exception> getExceptions() {
      return exceptions;
    }
  }

  /** excel 字段信息 */
  public static class ExcelFieldInfo {

    private String fieldName;

    private String originFieldType;

    public String getFieldName() {
      return fieldName;
    }

    public void setFieldName(String fieldName) {
      this.fieldName = fieldName;
    }

    public String getOriginFieldType() {
      return originFieldType;
    }

    public void setOriginFieldType(String originFieldType) {
      this.originFieldType = originFieldType;
    }
  }

  public Map<Integer, T> getCfgBeanMap() {
    return Collections.unmodifiableMap(cfgBeanMap);
  }

  public List<Exception> getExceptionCollectors() {
    return exceptionCollectors;
  }

  public List<T> getCfgBeanList() {
    return Collections.unmodifiableList(new ArrayList<>(cfgBeanMap.values()));
  }

  public Map<String, String> getMd5CacheMap() {
    return Collections.unmodifiableMap(md5CacheMap);
  }

  public int getFieldTypeRow() {
    return fieldTypeRow;
  }

  public int getFieldNameRow() {
    return fieldNameRow;
  }

  public int getFieldDataRangeRow() {
    return fieldDataRangeRow;
  }

  public int getDataStartRow() {
    return dataStartRow;
  }
}
